--更新五级部门视图
alter view view_bd_group
as
SELECT
	d1.dep_name AS deptName1,
	d1.dep_serial AS deptId1,
	d1.dep_no_add as deptCode1,
	d2.dep_name AS deptName2,
	d2.dep_serial AS deptId2,
	d2.dep_no_add as deptCode2,
	d3.dep_name AS deptName3,
	d3.dep_serial AS deptId3,
	d3.dep_no_add as deptCode3,
	d4.dep_name AS deptName4,
	d4.dep_serial AS deptId4,
	d4.dep_no_add as deptCode4,
	d5.dep_name AS deptName5,
	d5.dep_serial AS deptId5,
	d5.dep_no_add as deptCode5
FROM
	dt_dep d5
	LEFT JOIN dt_dep d4 ON d4.dep_serial= d5.dep_parent
	LEFT JOIN dt_dep d3 ON d3.dep_serial= d4.dep_parent
	LEFT JOIN dt_dep d2 ON d2.dep_serial= d3.dep_parent
	LEFT JOIN dt_dep d1 ON d1.dep_serial= d2.dep_parent
WHERE
	d5.dep_serial NOT IN (
	SELECT DISTINCT
		dep_parent
	FROM
	dt_dep
	)

;
go

--更新排班数据视图
alter view view_bd_group
as
SELECT
	b.user_no,
	b.user_serial,
	g.deptId5 AS dept_serial,
	g.deptName5 AS dept_name,
	g.deptCode5 as deptCode,
	g.deptName4 AS deptName4,
	g.deptName1 AS deptName1,
	a.rq,
	a.year,
	a.month,
	b.user_lname AS user_name,
	isnull( a.d1, '' ) d1,
	isnull( a.d2, '' ) d2,
	isnull( a.d3, '' ) d3,
	isnull( a.d4, '' ) d4,
	isnull( a.d5, '' ) d5,
	isnull( a.d6, '' ) d6,
	isnull( a.d7, '' ) d7,
	isnull( a.d8, '' ) d8,
	isnull( a.d9, '' ) d9,
	isnull( a.d10, '' ) d10,
	isnull( a.d11, '' ) d11,
	isnull( a.d12, '' ) d12,
	isnull( a.d13, '' ) d13,
	isnull( a.d14, '' ) d14,
	isnull( a.d15, '' ) d15,
	isnull( a.d16, '' ) d16,
	isnull( a.d17, '' ) d17,
	isnull( a.d18, '' ) d18,
	isnull( a.d19, '' ) d19,
	isnull( a.d20, '' ) d20,
	isnull( a.d21, '' ) d21,
	isnull( a.d22, '' ) d22,
	isnull( a.d23, '' ) d23,
	isnull( a.d24, '' ) d24,
	isnull( a.d25, '' ) d25,
	isnull( a.d26, '' ) d26,
	isnull( a.d27, '' ) d27,
	isnull( a.d28, '' ) d28,
	isnull( a.d29, '' ) d29,
	isnull( a.d30, '' ) d30,
	isnull( a.d31, '' ) d31,
	c.user_lname AS gly_name
FROM
	kt_paiba a
	INNER JOIN dt_user b ON a.user_serial= b.user_serial
	LEFT JOIN dt_user c ON a.gly_no= CONVERT ( VARCHAR ( 50 ), c.user_serial )
	LEFT JOIN view_bd_group g on g.deptId5 = b.user_dep
WHERE
	( b.dep_no IS NOT NULL )
	AND ( b.user_type<= 50 )
	OR (
	a.rq IS NULL)

;GO
--更新排班导出数据视图
alter view view_bd_export_schedule
as
SELECT
	b.user_no,
	b.user_serial,
	g.deptId5 AS dept_serial,
	g.deptName5 AS dept_name,
	g.deptCode5 as deptCode,
	g.deptName4 AS deptName4,
	g.deptName1 AS deptName1,
	a.rq,
	a.year,
	a.month,
	b.user_lname AS user_name,
	SUBSTRING (
		isnull( d1, '' ),
		CHARINDEX(
			'|',
		isnull( d1, '' )) + 1,
		len(
		isnull( d1, '' ))) AS d1,
	SUBSTRING (
		isnull( d2, '' ),
		CHARINDEX(
			'|',
		isnull( d2, '' )) + 1,
		len(
		isnull( d2, '' ))) AS d2,
	SUBSTRING (
		isnull( d3, '' ),
		CHARINDEX(
			'|',
		isnull( d3, '' )) + 1,
		len(
		isnull( d3, '' ))) AS d3,
	SUBSTRING (
		isnull( d4, '' ),
		CHARINDEX(
			'|',
		isnull( d4, '' )) + 1,
		len(
		isnull( d4, '' ))) AS d4,
	SUBSTRING (
		isnull( d5, '' ),
		CHARINDEX(
			'|',
		isnull( d5, '' )) + 1,
		len(
		isnull( d5, '' ))) AS d5,
	SUBSTRING (
		isnull( d6, '' ),
		CHARINDEX(
			'|',
		isnull( d6, '' )) + 1,
		len(
		isnull( d6, '' ))) AS d6,
	SUBSTRING (
		isnull( d7, '' ),
		CHARINDEX(
			'|',
		isnull( d7, '' )) + 1,
		len(
		isnull( d7, '' ))) AS d7,
	SUBSTRING (
		isnull( d8, '' ),
		CHARINDEX(
			'|',
		isnull( d8, '' )) + 1,
		len(
		isnull( d8, '' ))) AS d8,
	SUBSTRING (
		isnull( d9, '' ),
		CHARINDEX(
			'|',
		isnull( d9, '' )) + 1,
		len(
		isnull( d9, '' ))) AS d9,
	SUBSTRING (
		isnull( d10, '' ),
		CHARINDEX(
			'|',
		isnull( d10, '' )) + 1,
		len(
		isnull( d10, '' ))) AS d10,
	SUBSTRING (
		isnull( d11, '' ),
		CHARINDEX(
			'|',
		isnull( d11, '' )) + 1,
		len(
		isnull( d11, '' ))) AS d11,
	SUBSTRING (
		isnull( d12, '' ),
		CHARINDEX(
			'|',
		isnull( d12, '' )) + 1,
		len(
		isnull( d12, '' ))) AS d12,
	SUBSTRING (
		isnull( d13, '' ),
		CHARINDEX(
			'|',
		isnull( d13, '' )) + 1,
		len(
		isnull( d13, '' ))) AS d13,
	SUBSTRING (
		isnull( d14, '' ),
		CHARINDEX(
			'|',
		isnull( d15, '' )) + 1,
		len(
		isnull( d14, '' ))) AS d14,
	SUBSTRING (
		isnull( d15, '' ),
		CHARINDEX(
			'|',
		isnull( d15, '' )) + 1,
		len(
		isnull( d15, '' ))) AS d15,
	SUBSTRING (
		isnull( d16, '' ),
		CHARINDEX(
			'|',
		isnull( d16, '' )) + 1,
		len(
		isnull( d16, '' ))) AS d16,
	SUBSTRING (
		isnull( d17, '' ),
		CHARINDEX(
			'|',
		isnull( d17, '' )) + 1,
		len(
		isnull( d17, '' ))) AS d17,
	SUBSTRING (
		isnull( d18, '' ),
		CHARINDEX(
			'|',
		isnull( d18, '' )) + 1,
		len(
		isnull( d18, '' ))) AS d18,
	SUBSTRING (
		isnull( d19, '' ),
		CHARINDEX(
			'|',
		isnull( d19, '' )) + 1,
		len(
		isnull( d19, '' ))) AS d19,
	SUBSTRING (
		isnull( d20, '' ),
		CHARINDEX(
			'|',
		isnull( d20, '' )) + 1,
		len(
		isnull( d20, '' ))) AS d20,
	SUBSTRING (
		isnull( d21, '' ),
		CHARINDEX(
			'|',
		isnull( d21, '' )) + 1,
		len(
		isnull( d21, '' ))) AS d21,
	SUBSTRING (
		isnull( d22, '' ),
		CHARINDEX(
			'|',
		isnull( d22, '' )) + 1,
		len(
		isnull( d22, '' ))) AS d22,
	SUBSTRING (
		isnull( d23, '' ),
		CHARINDEX(
			'|',
		isnull( d23, '' )) + 1,
		len(
		isnull( d23, '' ))) AS d23,
	SUBSTRING (
		isnull( d24, '' ),
		CHARINDEX(
			'|',
		isnull( d24, '' )) + 1,
		len(
		isnull( d24, '' ))) AS d24,
	SUBSTRING (
		isnull( d25, '' ),
		CHARINDEX(
			'|',
		isnull( d25, '' )) + 1,
		len(
		isnull( d25, '' ))) AS d25,
	SUBSTRING (
		isnull( d26, '' ),
		CHARINDEX(
			'|',
		isnull( d26, '' )) + 1,
		len(
		isnull( d26, '' ))) AS d26,
	SUBSTRING (
		isnull( d27, '' ),
		CHARINDEX(
			'|',
		isnull( d27, '' )) + 1,
		len(
		isnull( d27, '' ))) AS d27,
	SUBSTRING (
		isnull( d28, '' ),
		CHARINDEX(
			'|',
		isnull( d28, '' )) + 1,
		len(
		isnull( d28, '' ))) AS d28,
	SUBSTRING (
		isnull( d29, '' ),
		CHARINDEX(
			'|',
		isnull( d29, '' )) + 1,
		len(
		isnull( d29, '' ))) AS d29,
	SUBSTRING (
		isnull( d30, '' ),
		CHARINDEX(
			'|',
		isnull( d30, '' )) + 1,
		len(
		isnull( d30, '' ))) AS d30,
	SUBSTRING (
		isnull( d31, '' ),
		CHARINDEX(
			'|',
		isnull( d31, '' )) + 1,
		len(
		isnull( d31, '' ))) AS d31,
	c.user_lname AS gly_name
FROM
	kt_paiba a
	INNER JOIN dt_user b ON a.user_serial= b.user_serial
	LEFT JOIN dt_user c ON a.gly_no= CONVERT ( VARCHAR ( 50 ), c.user_serial )
	LEFT JOIN view_bd_group g on g.deptId5 = b.user_dep
WHERE
	( b.dep_no IS NOT NULL )
	AND ( b.user_type<= 50 )
	OR (
	a.rq IS NULL)
;go